<?php
/**
 * Smart Restaurant
 *
 * An open source application to manage restaurants
 *
 * @package		SmartRestaurant
 * @author		Gjergj Sheldija
 * @copyright	Copyright (c) 2008, Gjergj Sheldija
 * @license		http://www.gnu.org/licenses/gpl.txt
 * @since		Version 1.0
 * @filesource
 * 
 *  Smart Restaurant is free software: you can redistribute it and/or modify
 *	it under the terms of the GNU General Public License as published by
 *	the Free Software Foundation, version 3 of the License.
 *
 *	Smart Restaurant is distributed in the hope that it will be useful,
 *	but WITHOUT ANY WARRANTY; without even the implied warranty of
 *	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *	GNU General Public License for more details.

 *	You should have received a copy of the GNU General Public License
 *	along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */
/**
 * @author : mizuko
 * report for what do i print in the bar
 * presumibly the stock of the bar
 */
require_once( 'Spreadsheet\Excel\Writer.php' );

$con = mysql_connect("localhost" , "root" , "") or die ("Not Fouund" . mysql_error());
mysql_select_db("resplus_acc") or die ("Not Found" . mysql_error());
$select = 'SELECT `stock_objects`.`name` AS \'Emri\','
        . ' CASE WHEN `stock_objects`.`unit_type` = \'0\' THEN \'Cope\' WHEN `stock_objects`.`unit_type` = \'1\' THEN \'KG\' WHEN `stock_objects`.`unit_type` = \'2\' THEN \'Liter\' END AS `Njesia`,'
        . ' ROUND(`stock_objects`.`quantity`, 2) AS `Sasia Aktuale`,'
        . ' if(`ingreds`.`sell_price` = 0,ROUND(SUM(`dishes`.`price` / `stock_ingredient_quantities`.`quantity`),2),`ingreds`.`sell_price`) AS `Cmimi i Shitjes`,'
        . ' ROUND(SUM(`stock_objects`.`value`), 2) AS `Cmimi i Blerjes`,'
        . ' if(`ingreds`.`sell_price` = 0,ROUND(SUM(`dishes`.`price` / `stock_ingredient_quantities`.`quantity`)*SUM(`stock_objects`.`quantity`),2),ROUND(`ingreds`.`sell_price`*`stock_objects`.`quantity`,2)) AS `Vlera Cmimi i Shitjes`,'
        . ' ROUND((SUM(`stock_objects`.`quantity`) * `stock_objects`.`value`),2) AS `Vlera Cmimi i Blerjes`, '
        . ' COUNT(`dishes`.`id`) AS `TOTALI`'
        . ' FROM `stock_objects`'
        . ' INNER JOIN `stock_ingredient_quantities`'
        . ' ON `stock_objects`.`id` = `stock_ingredient_quantities`.`obj_id`'
        . ' INNER JOIN `dishes` ON `stock_ingredient_quantities`.`dish_id` = `dishes`.`id`'
        . ' inner join `ingreds`on `ingreds`.`id` = `stock_objects`.`ref_id`'
        . ' WHERE `dishes`.`destid` = \'3\''
        . ' AND `stock_objects`.`deleted` = \'0\''
        . ' AND `dishes`.`deleted` = \'0\''
        . ' GROUP BY `stock_objects`.`name`, `stock_objects`.`unit_type`'
        . ' ORDER BY `dishes`.`category`, `dishes`.`name`';

$export = mysql_query($select); 
$fields = mysql_num_fields($export); 

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

$emen = 'bari.xls';
// sending HTTP headers
$workbook->send($emen);

// Creating a worksheet
$worksheet =& $workbook->addWorksheet('Inventari Fizik - Bari');

//gjansia e kolonave
$worksheet->setColumn(0,0,3);
$worksheet->setColumn(0,1,20);
$worksheet->setColumn(1,2,8);
$worksheet->setColumn(2,3,8);
$worksheet->setColumn(3,4,8);
$worksheet->setColumn(4,5,8);
$worksheet->setColumn(5,6,14);
$worksheet->setColumn(6,7,14);


//krijoj formatin me perdore mandej
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_bold->setTextWrap();


//krijoj kokat e tabelave
$worksheet->write(0,0, 'Nr' , $format_bold ); 
$worksheet->write(0,1, 'Emri' , $format_bold ); 
$worksheet->write(0,2, 'Njesia'  , $format_bold ); 
$worksheet->write(0,3, 'Sasia Aktuale'  , $format_bold ); 
$worksheet->write(0,4, 'Cmimi Shitjes' , $format_bold ); 
$worksheet->write(0,5, 'Cmimi Blerjes'  , $format_bold ); 
$worksheet->write(0,6, 'Vlera Cmimi i Shitjes' , $format_bold ); 
$worksheet->write(0,7, 'Vlera Cmimi i Blerjes', $format_bold ); 
$worksheet->write(0,7, 'Vlera Cmimi i Blerjes', $format_bold ); 

$rreshti;

for ($i = 1 ; $row = mysql_fetch_assoc($export) ; $i++) {
    $worksheet->write($i,0, $i );     
	$worksheet->write($i,1, $row['Emri']); 
    $worksheet->write($i,2, $row['Njesia'] ); 
    $worksheet->write($i,3, $row['Sasia Aktuale'] );     
    $worksheet->write($i,4, sprintf("%01.0f", $row['Cmimi i Shitjes']));
    $worksheet->write($i,5, sprintf("%01.0f", $row['Cmimi i Blerjes']));     
    $worksheet->write($i,6, sprintf("%01.0f",($row['Vlera Cmimi i Shitjes'])));
	$worksheet->write($i,7, sprintf("%01.0f",($row['Vlera Cmimi i Blerjes']))) ;       
    $rreshti = $i;
} 

$rreshti+=1;

//llogaris shumen e kolonave
$worksheet->writeFormula($rreshti, 6, "=SUM(G1:G".$rreshti.")", $format_bold);
$worksheet->writeFormula($rreshti, 7, "=SUM(H1:H".$rreshti.")", $format_bold);


$workbook->close();
?>
